package com.rmb.dataanalysis.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.druid.util.StringUtils;
import com.rmb.common.exception.ExceptionCast;
import com.rmb.common.model.assist.AssistQuery;
import com.rmb.common.model.response.CommonCode;
import com.rmb.common.model.sensitive.Desensitization;
import com.rmb.common.model.sensitive.NameUtil;
import com.rmb.common.model.state.TaskStatus;
import com.rmb.dataanalysis.client.DataSourceServiceClient;
import com.rmb.dataanalysis.dao.DataAnalysisDao;
import com.rmb.dataanalysis.entity.TbAnalysis;
import com.rmb.dataanalysis.mapper.TbAnalysisMapper;
import com.rmb.dataanalysis.service.DataAnalysisService;
import com.rmb.dataanalysis.verify.JdbcDriver;
import com.rmb.dataanalysis.verify.SqlFormatUtil;
import com.rmb.entity.SourceEntity;
import com.rmb.fremarker.vague.FuzzyQuery;
import com.rmb.util.JdbcConnector;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * @version 1.0
 * @Author ZhaoMuYang
 * @Date 2021/1/12 14:52
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class DataAnalysisServiceImpl implements DataAnalysisService {

    //public static final List<Map<String, String>> QUERY_LIKE_DATA = new ConcurrentHashMap<>(64);

    public static final List<Map<String,Object>> QUERY_LIKE_DATA=new ArrayList<>();
    @Autowired
    private DataSourceServiceClient dataSourceServiceClient;

    @Resource
    private DataAnalysisDao dataAnalysisDao;

    @Resource
    private TbAnalysisMapper tbAnalysisMapper;

    @Override
    public List<Map<String,Object>> findAllDataSource() {

        return dataSourceServiceClient.getAllDataSource();
    }


    @Override
    public List<String> findAllDataBase(Long dataSourceId) {
        if(dataSourceId==null){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        return dataSourceServiceClient.findAllDataBase(dataSourceId);
    }


    @Override
    public List<String> findTablesByDataSource(Long dataCourseId, String dataBaseName) {
        if(dataCourseId==null){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        if(StringUtils.isEmpty(dataBaseName)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }

        return dataSourceServiceClient.findTablesByDataSource(dataCourseId,dataBaseName);
    }

    @Override
    public List<Map<String, Object>> executeSqlQuery(Long dataSourceId, String dataBaseName,String tableName ,String sql) {

        if(dataSourceId==null){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }

        if(StringUtils.isEmpty(dataBaseName)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }

        if(StringUtils.isEmpty(tableName)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }

        if(StringUtils.isEmpty(sql)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }

        if(!SqlFormatUtil.sqlFormat(sql)){
            ExceptionCast.cast(CommonCode.SQL_WRONG_FORMAT);
        }

        return dataSourceServiceClient.executeSqlQuery(dataSourceId,dataBaseName,tableName,sql);
    }


    @Override
    public int addAnalysis(String executeSql, String executeResult, Long dataSourceId, String dataBaseName, String tableName) {
        if(StringUtils.isEmpty(executeSql)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        if(StringUtils.isEmpty(executeResult)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        if(dataSourceId==null){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        if(StringUtils.isEmpty(dataBaseName)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        if(StringUtils.isEmpty(tableName)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }

        return tbAnalysisMapper.insert(new TbAnalysis(null, executeSql, executeResult, dataSourceId, dataBaseName, tableName, new Date(), new Date(), Desensitization.MY_NAME, Desensitization.MY_NAME));
    }

    @Override
    public String getDataExcel( Long dataSourceId, String dataBaseName, String tableName) {
        if(dataSourceId==null){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        if(StringUtils.isEmpty(dataBaseName)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        if(StringUtils.isEmpty(tableName)){
            ExceptionCast.cast(CommonCode.PARAM_FALL);
        }
        Map<String, Object> printMap = new LinkedHashMap<>();

        SourceEntity source = dataSourceServiceClient.getDataSourceById(dataSourceId);

        Connection connection =null;
        ResultSet resultSet =null;
        Statement statement=null;
        try {
            Class.forName(JdbcDriver.JDBC_DRIVER);
            String url = JdbcDriver.JDBC_URL + source.getHost() + AssistQuery.JDBC_CONNECT.getContent() + source.getPort() + AssistQuery.ENCODE_UNDERLINE.getContent() + source.getDatabaseName() + "?serverTimezone=Asia/Shanghai";
            System.out.println(url);
            connection = DriverManager.getConnection(url, source.getUsername(), source.getPassword());

            statement= connection.createStatement();
            statement.execute("use "+dataBaseName);
            resultSet = statement.executeQuery("SELECT * FROM " + tableName);

            while(resultSet.next()){
                //表结构
                ResultSetMetaData metaData = resultSet.getMetaData();
                //表列数
                int columnCount = metaData.getColumnCount();

                for (int i = TaskStatus.EXECUTOR_COTACT_STATUS.getStatus(); i <= columnCount; i++){
                    //表字段
                    String columnLabel = metaData.getColumnLabel(i);
                    printMap.put(columnLabel,  resultSet.getString(i));
                }
            }
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                assert connection != null;
                connection.close();
                assert resultSet != null;
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(printMap);

        String fileName=System.getProperty("user.dir")+ NameUtil.getRandom(7, NameUtil.TYPE.LETTER_CAPITAL_NUMBER) +".xlsx";
        //创建excel
        ExcelWriter writer = ExcelUtil.getWriter(fileName);

        writer.merge(printMap.size() - TaskStatus.EXECUTOR_COTACT_STATUS.getStatus(), "BI分析记录");

        writer.write(rows, true);

        writer.close();
        return fileName;
    }

}
